﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using QualityRpt.Utils;
using System.Data.Odbc;
using Org.SqlHelper;
using System.Web.Caching;
using System.Web;

namespace QualityRpt.Dao.Db2
{
    class AirwirePlazaDao:IAirwirePlazaDao
    {
        const string cachePrefix = "APL-";
        public DataTable GetAllLane()
        {
            DataTable rtTable = null;
            StringBuilder sbText = new StringBuilder();
            sbText.Append("SELECT DISTINCT(LANE_NO) FROM RPT.AIRWIRE_PLAZA ");
            string cacheKey = cachePrefix + sbText.ToString();
            if (CacheManager.Get(cacheKey) == null)
            {
                using (OdbcConnection conn = ODBCHelper.GetConnection())
                {
                    rtTable = ODBCHelper.ReadTable(conn, CommandType.Text, sbText.ToString());
                }
                CacheManager.Put(cacheKey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cacheKey) as DataTable;
            }
            return rtTable;
        }

        public DataRow Save(DataRow row)
        {
            object rt = null;
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO RPT.AIRWIRE_PLAZA(PLAZA_ID,PLAZA_NAME,ROAD_ID,ROAD_NAME,LANE_NO,AIRWIRE_SUPPLIER_ID,AIRWIRE_SUPPLIER_NAME"+
                    ",AIRWIRE_SPEC_ID,AIRWIRE_SPEC_NAME,AIRWIRE_SOFTVERSION,AIRWIRE_HARDVERSION,LANE_MODE,AIRWIRE_TYPE,START_TIME,END_TIME,CREATED_TIME,REMARK) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                    ODBCHelper.CreateInputParameter("PLAZA_ID", OdbcType.Int, Convert.ToInt32(row["PLAZA_ID"])),
                    ODBCHelper.CreateInputParameter("PLAZA_NAME", OdbcType.VarChar,200, row["PLAZA_NAME"]),
                    ODBCHelper.CreateInputParameter("ROAD_ID", OdbcType.Int, row["ROAD_ID"]),
                    ODBCHelper.CreateInputParameter("ROAD_NAME", OdbcType.VarChar, 200, row["ROAD_NAME"]),
                    ODBCHelper.CreateInputParameter("LANE_NO", OdbcType.Int,row["LANE_NO"]),
                    ODBCHelper.CreateInputParameter("AIRWIRE_SUPPLIER_ID", OdbcType.Int, row["AIRWIRE_SUPPLIER_ID"]),
                    ODBCHelper.CreateInputParameter("AIRWIRE_SUPPLIER_NAME", OdbcType.VarChar,200, row["AIRWIRE_SUPPLIER_NAME"]),
                    ODBCHelper.CreateInputParameter("AIRWIRE_SPEC_ID", OdbcType.Int, row["AIRWIRE_SPEC_ID"]),
                    ODBCHelper.CreateInputParameter("AIRWIRE_SPEC_NAME", OdbcType.VarChar,200, row["AIRWIRE_SPEC_NAME"]),
                    ODBCHelper.CreateInputParameter("AIRWIRE_SOFTVERSION", OdbcType.VarChar,50, row["AIRWIRE_SOFTVERSION"]),
                    ODBCHelper.CreateInputParameter("AIRWIRE_HARDVERSION", OdbcType.VarChar,50, row["AIRWIRE_HARDVERSION"]),
                    ODBCHelper.CreateInputParameter("LANE_MODE", OdbcType.VarChar,50, row["LANE_MODE"]),
                    ODBCHelper.CreateInputParameter("AIRWIRE_TYPE", OdbcType.VarChar,50, row["AIRWIRE_TYPE"]),
                    ODBCHelper.CreateInputParameter("START_TIME", OdbcType.Date, row["START_TIME"]),
                    ODBCHelper.CreateInputParameter("END_TIME", OdbcType.Date, row["END_TIME"]),
                    ODBCHelper.CreateInputParameter("CREATED_TIME", OdbcType.Date, row["CREATED_TIME"]),
                    ODBCHelper.CreateInputParameter("REMARK", OdbcType.VarChar, 50, row["REMARK"])
                    );
                rt = ODBCHelper.ExecuteScalar(conn, CommandType.Text, "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1");
            }
            CacheManager.RemoveByPrefix(cachePrefix);
            if (rt != null)
            {
                row["ID"] = Convert.ToInt32(rt);
                return row;
            }
            return null;
        }

        public DataRow Update(DataRow row)
        {
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "UPDATE RPT.AIRWIRE_PLAZA SET PLAZA_ID=?,PLAZA_NAME=?,ROAD_ID=?,ROAD_NAME=?,LANE_NO=?,AIRWIRE_SUPPLIER_ID=?,AIRWIRE_SUPPLIER_NAME=?" +
                    ",AIRWIRE_SPEC_ID=?,AIRWIRE_SPEC_NAME=?,AIRWIRE_SOFTVERSION=?,AIRWIRE_HARDVERSION=?,LANE_MODE=?,AIRWIRE_TYPE=?,START_TIME=?,END_TIME=?,CREATED_TIME=?,REMARK=? WHERE ID=?",
                    ODBCHelper.CreateInputParameter("PLAZA_ID", OdbcType.Int, Convert.ToInt32(row["PLAZA_ID"])),
                    ODBCHelper.CreateInputParameter("PLAZA_NAME", OdbcType.VarChar, 200, row["PLAZA_NAME"]),
                    ODBCHelper.CreateInputParameter("ROAD_ID", OdbcType.Int, row["ROAD_ID"]),
                    ODBCHelper.CreateInputParameter("ROAD_NAME", OdbcType.VarChar, 200, row["ROAD_NAME"]),
                    ODBCHelper.CreateInputParameter("LANE_NO", OdbcType.Int, row["LANE_NO"]),
                    ODBCHelper.CreateInputParameter("AIRWIRE_SUPPLIER_ID", OdbcType.Int, row["AIRWIRE_SUPPLIER_ID"]),
                    ODBCHelper.CreateInputParameter("AIRWIRE_SUPPLIER_NAME", OdbcType.VarChar, 200, row["AIRWIRE_SUPPLIER_NAME"]),
                    ODBCHelper.CreateInputParameter("AIRWIRE_SPEC_ID", OdbcType.Int, row["AIRWIRE_SPEC_ID"]),
                    ODBCHelper.CreateInputParameter("AIRWIRE_SPEC_NAME", OdbcType.VarChar, 200, row["AIRWIRE_SPEC_NAME"]),
                    ODBCHelper.CreateInputParameter("AIRWIRE_SOFTVERSION", OdbcType.VarChar, 50, row["AIRWIRE_SOFTVERSION"]),
                    ODBCHelper.CreateInputParameter("AIRWIRE_HARDVERSION", OdbcType.VarChar, 50, row["AIRWIRE_HARDVERSION"]),
                    ODBCHelper.CreateInputParameter("LANE_MODE", OdbcType.VarChar, 50, row["LANE_MODE"]),
                    ODBCHelper.CreateInputParameter("AIRWIRE_TYPE", OdbcType.VarChar, 50, row["AIRWIRE_TYPE"]),
                    ODBCHelper.CreateInputParameter("START_TIME", OdbcType.Date, row["START_TIME"]),
                    ODBCHelper.CreateInputParameter("END_TIME", OdbcType.Date, row["END_TIME"]),
                    ODBCHelper.CreateInputParameter("CREATED_TIME", OdbcType.Date, row["CREATED_TIME"]),
                    ODBCHelper.CreateInputParameter("REMARK", OdbcType.VarChar, 50, row["REMARK"]),
                    ODBCHelper.CreateInputParameter("ID", OdbcType.Int, row["ID"])
                    );
            }
            CacheManager.RemoveByPrefix(cachePrefix);
            return row;
        }

        public DataRow Get(long id)
        {
            DataTable rtTable = GetList(" ID="+id);
            if (rtTable.Rows.Count > 0)
            {
                return rtTable.Rows[0];
            }
            return null;
        }

        public void Delete(long id)
        {
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "DELETE FROM RPT.AIRWIRE_PLAZA WHERE ID=?",
                    ODBCHelper.CreateInputParameter("ID", OdbcType.Int, id));
            }
            CacheManager.RemoveByPrefix(cachePrefix);
        }

        public DataTable GetList(string where)
        {
            return GetList(where, string.Empty);
        }

        public DataTable GetList(string where, string sort)
        {
            DataTable rtTable = null;
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("SELECT A.* FROM RPT.AIRWIRE_PLAZA AS A");
            if (where.Trim() != string.Empty)
            {
                sbSql.Append(" WHERE "+where);
            }
            if (sort.Trim() != string.Empty)
            {
                sbSql.Append(" ORDER BY "+sort);
            }
            string cacheKey = cachePrefix + sbSql.ToString();
            if (CacheManager.Get(cacheKey) == null)
            {
                using (OdbcConnection conn = ODBCHelper.GetConnection())
                {
                    rtTable = ODBCHelper.ReadTable(conn, CommandType.Text, sbSql.ToString());
                }
                CacheManager.Put(cacheKey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cacheKey) as DataTable;
            }
            return rtTable;
        }
        public DataTable GetListBySpRd(string spIn, string rdIn,PageUtils pu)
        {
            StringBuilder sbSql = new StringBuilder();
            if (spIn != null && spIn.Trim() != string.Empty)
            {
                sbSql.Append(" A.AIRWIRE_SUPPLIER_ID IN ("+spIn+")");
            }
            if (rdIn != null && rdIn.Trim() != string.Empty)
            {
                sbSql.Append(" AND A.ROAD_ID IN("+rdIn+")");
            }
            if (pu == null)
            {
                return GetList(sbSql.ToString());
            }
            else
            {
                return GetPageList(pu, sbSql.ToString(), string.Empty);
            }
        }
        public DataTable GetPageList(PageUtils pu, string where, string sort)
        {
            DataTable rtTable = null;
            StringBuilder sbSql = new StringBuilder();
            StringBuilder sbCountSql = new StringBuilder();
            sbSql.Append("SELECT A.* FROM RPT.AIRWIRE_PLAZA AS A");
            if (where.Trim() != string.Empty)
            {
                sbSql.Append(" WHERE " + where);
            }
            sbCountSql.Append("WITH WT AS (" + sbSql.ToString() + ") SELECT COUNT(*) FROM WT ");
            if (sort.Trim() != string.Empty)
            {
                sbSql.Append(" ORDER BY " + sort);
            }
            else
            {
                sbSql.Append(" ORDER BY A.ID DESC");
            }
            string cachekey = cachePrefix + sbSql.ToString() + "-" + pu.Page.ToString();
            pu.ListSql = sbSql.ToString(); pu.TotalCountSql = sbCountSql.ToString();
            if (CacheManager.Get(cachekey) == null)
            {
                rtTable = pu.GetDb2OdbcPageList();
                CacheManager.Put(cachekey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cachekey) as DataTable;
                pu.DB2OdbcExecutePage();
            }
            return rtTable;
        }
    }
}
